#pragma once
#include <memory>
#include <odb/exception.hxx>
#include <odb/exceptions.hxx>
#include <odb/mysql/database.hxx>
#include <odb/result.hxx>
#include <odb/transaction.hxx>
#include <log.h>
#include <string>
#include <vector>

#include "Blog.hpp"
#include "Blog-odb.hxx"

using namespace std;

namespace lyt{
    class BlogData{
    private:
        std::shared_ptr<odb::mysql::database> db;
    public:
        using ptr = shared_ptr<BlogData>;
        BlogData(const shared_ptr<odb::mysql::database>& client):db(client){}
// ========================BlogPreview-begin===============================
        bool insert_blog(Blog& blog){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                db->persist(blog);
                transa.commit();
                return true;
            }catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        // 按照文章id查找
        bool query_blog(const unsigned long long& id,Blog& blog){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                // db->load()返回是一个unique_ptr
                blog = *db->load<Blog>(id); 
                transa.commit();
                return true;
            }catch(const odb::object_not_persistent& e){
                // 没找到对象
                transa.commit();
                debuglog << e.what();
                return false;
            }catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        
        // 查找博客作者
        bool query_blog_author_id(const unsigned long long& blog_id,std::string& blog_author_id){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                blog_author_id = db->load<Blog>(blog_id)->author_id;
                
                transa.commit();
                return true;                        
            } catch(const odb::object_not_persistent& e){
                // 没找到对象
                transa.commit();
                debuglog << e.what();
                return false;
            }catch (const odb::exception& e) {                                                                                                                                                                                                                                                                                          
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        // 根据页码和每页数量查找(分页查询)，注意查询的信息发布状态必须是已经发布的信息
        bool query_blogs(const int& page,const int& size,const std::string& email,const bool& published,std::vector<Blog>& info){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                using query = odb::query<Blog>;
                using result = odb::result<Blog>;

                // 计算读取数据的偏移位置
                int offset = (page - 1)*size;
                // SELECT * FROM blog_preview
                // WHERE TRUE
                // ORDER BY date DESC
                // LIMIT :size
                // OFFSET :offset
                result res;
                if(email.empty()){
                    // 如果email为空，表示查询的是主页的博客列表
                    res = db->query<Blog>(query(query::published == published)
                        + "ORDER BY" + query::date + "DESC" + "LIMIT" + query::_val(size)
                        + "OFFSET" + query::_val(offset));
                }else{
                    // 如果email不为空，表示查询的是用户自己的博客列表
                    res = db->query<Blog>(query(query::author_id == email && query::published == published)
                        + "ORDER BY" + query::date + "DESC" + "LIMIT" + query::_val(size)
                        + "OFFSET" + query::_val(offset));
                }
                // 填充结果
                for(auto& blog:res){
                    info.emplace_back(std::move(blog));
                }
                transa.commit();
                return true;
            }catch(const odb::exception& e){
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            }catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool query_blog_by_ids(const std::vector<unsigned long long>& blog_ids,std::vector<Blog>& blogs){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                if (blog_ids.empty()) {
                    transa.commit();  // 无需查询，提前返回
                    return true;
                }
                using query = odb::query<Blog>;
                using result = odb::result<Blog>;
                result res = db->query<Blog>(query::id.in_range(blog_ids.begin(),blog_ids.end()));

                for (const auto& blog : res) {
                    blogs.push_back(blog);
                }

                transa.commit();
                return true;
            }catch(const odb::exception& e){
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            }catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool update_blog(const Blog& blog) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());

                // 3. 更新对象到数据库
                db->update(blog);
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "更新likes失败: " << e.what();
                return false;
            }catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool update_blog_likes(const unsigned long long& blog_id) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());

                db->execute("UPDATE Blog SET likes = likes + 1 WHERE id = " + std::to_string(blog_id));
                
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                errorlog << "更新likes失败: " << e.what();
                return false;
            }catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool update_blog_view_total(const unsigned long long& blog_id) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());

                db->execute("UPDATE Blog SET view_count = view_count + 1 WHERE id = " + std::to_string(blog_id));

                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                errorlog << "更新likes失败: " << e.what();
                return false;
            }catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool update_blog_comment_total(const unsigned long long& comment_id) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());

                db->execute("UPDATE Comment SET comment_total = comment_total + 1 WHERE id = " + std::to_string(comment_id));

                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                errorlog << "更新失败: " << e.what();
                return false;
            }catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool update_blog_view_count(const unsigned long long& blog_id) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());

                db->execute("UPDATE Blog SET view_count = view_count + 1 WHERE id = " + std::to_string(blog_id));

                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                errorlog << "更新likes失败: " << e.what();
                return false;
            }catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool delete_blog(const unsigned long long& id){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                using query = odb::query<Blog>;
                using result = odb::result<Blog>;
                db->erase_query<Blog>(query::id == id);
                transa.commit();
                return true;  
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool delete_blog_like(const unsigned long long& id){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                db->execute("UPDATE Blog SET likes = likes - 1 WHERE id = " + std::to_string(id));
                transa.commit();
                return true;  
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
// ========================BlogPreview-end===============================



        


// ========================Comment-begin===============================
        bool insert_blogcomment(Comment& comment){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                db->persist(comment);
                transa.commit();
                return true;
            }catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool query_blogcomment(const unsigned long long& id,Comment& comment){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                comment = *db->load<Comment>(id);
                transa.commit();
                return true;  
            } catch(const odb::object_not_persistent& e){
                // 没找到对象
                transa.commit();
                debuglog << e.what();
                return false;
            }catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool query_blogcomment_by_page(const int& page,const int& page_size,std::vector<Comment>& comment){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                using query = odb::query<Comment>;
                using result = odb::result<Comment>;

                int offset = (page - 1)* page_size;
                // SELECT * FROM blog_preview
                // WHERE TRUE
                // ORDER BY date DESC
                // LIMIT :size
                // OFFSET :offset
                result res = db->query<Comment>(query(true)
                            + "ORDER BY" + query::created_at + "DESC" + "LIMIT" + query::_val(page_size)
                            + "OFFSET" + query::_val(offset));
                // 填充结果
                for(auto& blog:res){
                    comment.emplace_back(std::move(blog));
                }
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool delete_comment(const unsigned long long& id){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                using query = odb::query<Comment>;
                db->erase_query<Comment>(query::id == id);
                transa.commit();
                return true;  
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        // 删除博客中的所有评论
        bool delete_comment_by_blog_id(const unsigned long long& blog_id){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                using query = odb::query<Comment>;
                db->erase_query<Comment>(query::blog_id == blog_id);
                transa.commit();
                return true;  
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        // 根据id和父评论查询comments表，根据时间倒叙筛选出对应的评论,然后再拿对应的id去Comment表中取出评论
        bool query_comments_by_page(const unsigned long long& blog_id,
                                 const unsigned long long& parent_id,
                                 const int& page,
                                 const int& page_size,
                                 std::vector<Comment>& info) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());
                using query = odb::query<Comment>;
                using result = odb::result<Comment>;

                int offset = (page - 1) * page_size;

                // 添加双重条件: 属于文章，且是指定父评论的子评论
                result res = db->query<Comment>(
                    query(query::blog_id == blog_id && query::parent_id == parent_id) +
                    " ORDER BY " + query::created_at + " DESC" +
                    " LIMIT " + query::_val(page_size) +
                    " OFFSET " + query::_val(offset)
                );

                for(auto& e:res){
                    info.emplace_back(std::move(e));
                }
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool query_comments_by_time(const unsigned long long& blog_id,
                                 const int& page,
                                 const int& page_size,
                                 std::vector<Comment>& info) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());
                using query = odb::query<Comment>;
                using result = odb::result<Comment>;

                int offset = (page - 1) * page_size;

                result res = db->query<Comment>(
                    query(query::blog_id == blog_id) +
                    " ORDER BY " + query::created_at + " DESC" +
                    " LIMIT " + query::_val(page_size) +
                    " OFFSET " + query::_val(offset)
                );
                
                debuglog << "查出的评论数量:" << res.size();
                for(auto& e:res){
                    info.emplace_back(std::move(e));
                }
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
// ========================Comment-end===============================


// ========================Comments-begin===============================
/*        bool insert_blogcomments(const Comments& comments){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                db->persist(comments);
                transa.commit();
                return true;
            }catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        // 按照文章id查找
        bool query_blogcomments(const unsigned long long& id,Comments& comments){
            odb::transaction transa;
            try{
                transa.reset(db->begin());
                using query = odb::query<Comments>;
                using result = odb::result<Comments>;
                result res(db->query<Comments>(query::id == id));
                if(!res.empty()){
                    comments = *res.begin();
                    transa.commit();
                    return true;
                }else{
                    transa.commit();
                    return false;
                }    
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        // 根据id和父评论查询comments表，根据时间倒叙筛选出对应的评论,然后再拿对应的id去Comment表中取出评论
        bool query_blogcomments_by_page(const unsigned long long& blog_id,
                                 const unsigned long long& parent_id,
                                 const int& page,
                                 const int& page_size,
                                 std::vector<Comment>& info) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());
                using query = odb::query<Comments>;
                using result = odb::result<Comments>;

                int offset = (page - 1) * page_size;

                // 添加双重条件: 属于文章，且是指定父评论的子评论
                result res = db->query<Comments>(
                    query(query::id == blog_id && query::comment_p_id == parent_id) +
                    " ORDER BY " + query::created_at + " DESC" +
                    " LIMIT " + query::_val(page_size) +
                    " OFFSET " + query::_val(offset)
                );

                std::vector<unsigned long long> ids;
                for (auto& e : res) {
                    ids.push_back(e.id);
                }

                query_blogcomments_by_ids(ids, info);
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        // 删除Comments中的评论时同时会删除Comment中的对应信息
        bool delete_blogcomments(const unsigned long long& blog_id) {
            odb::transaction transa;
            try {
                transa.reset(db->begin());
                using query = odb::query<Comments>;
                using result = odb::result<Comments>;

                result res = db->query<Comments>(query(query::id == blog_id));
                std::vector<unsigned long long> comment_ids;
                for (auto& e : res) {
                    comment_ids.push_back(e.comment_id);
                    db->erase(e);
                }
                if(!comment_ids.empty()){
                    using query = odb::query<Comment>;
                    for(const auto& e : comment_ids){
                        db->erase_query<Comment>(query::id == e);
                    }
                }
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
            */
// ========================Comments-end===============================
    

// ========================Like-begin===============================
        // 向表中插入数据
        bool inset_like(const Like& like){
            odb::transaction transa;
            try {
                transa.reset(db->begin());
                db->persist(like);
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool query_like(const LikeKey& likekey){
            odb::transaction transa;
            try {
                transa.reset(db->begin());
                // 如果对象不存在就会抛异常
                db->load<Like>(likekey);
                transa.commit();
                return true;
            } catch (const odb::object_not_persistent&) {
                // 对象不存在（主键查不到）
                transa.commit();  // 无需 rollback，没修改
                return false;
            }
        }
        // 根据page和page_size筛选出对应的文章id
        bool query_like_by_page(const std::string& email,const int& page,const int& page_size,std::vector<unsigned long long>& blogs_ids){
            odb::transaction transa;
            try {
                transa.reset(db->begin());
                using query = odb::query<Like>;
                using result = odb::result<Like>; 

                int offset = (page - 1) * page_size;
                result res = db->query<Like>(query(query::key.email == email)
                                                + "ORDER BY" + query::like_at + "DESC"
                                                + "LIMIT" + query::_val(page_size) 
                                                + "OFFSET" + query::_val(offset));
                for(auto& e:res){
                    blogs_ids.emplace_back(std::move(e.key.id));
                }
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }
        bool delete_like(const LikeKey& likekey){
            odb::transaction transa;
            try {
                transa.reset(db->begin());
                db->erase<Like>(likekey);
                transa.commit();
                return true;
            } catch (const odb::exception& e) {
                transa.rollback();
                errorlog << "ODB异常: " << e.what();
                return false;
            } catch (const std::exception& e) {
                transa.rollback();
                errorlog << "其他异常: " << e.what();
                return false;
            }
        }


// ========================Like-end===============================

    };
}